if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SmtpLogEntryGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].SmtpLogEntryGet
GO

CREATE   PROCEDURE dbo.[SmtpLogEntryGet]
(
	@dateTime datetime,
	@mailTo varchar(255),
	@thresholdSeconds int
)
AS

SET NOCOUNT ON

DECLARE @upperDateTimeRange datetime
DECLARE @lowerDateTimeRange datetime
DECLARE @upperInetLogRange int
DECLARE @lowerInetLogRange int
DECLARE @InetLogID int
DECLARE @ClientHost varchar(255)
DECLARE @MessageID varchar(500)

SET @upperDateTimeRange = DATEADD(s, @thresholdSeconds, @dateTime)
SET @lowerDateTimeRange = DATEADD(s, -@thresholdSeconds, @dateTime)

SELECT TOP 1
	@InetLogID = InetLogID,
	@ClientHost = ClientHost
FROM       
	inetlog
WHERE      
	LogTime >= @lowerDateTimeRange
	AND 
	LogTime <= @upperDateTimeRange
	AND
	operation = 'RCPT'
	--AND
	--servicestatus = 250
	AND 
	REPLACE(RTRIM(LTRIM(parameters)), ' ', '') = 'TO:<' + @mailTo + '>'

--PRINT '@InetLogID:  ' + CAST(@InetLogID AS varchar(20))
--PRINT '@ClientHost:  ' + CAST(@ClientHost AS varchar(20))

SET @lowerInetLogRange = @InetLogID + 1

SELECT TOP 1
	@upperInetLogRange = InetLogID + 2
FROM
	inetlog
WHERE
	InetLogID > @lowerInetLogRange
	AND
	username = 'OutboundConnectionCommand' 
	AND
	RTRIM(LTRIM(operation)) = 'DATA' 

IF (@upperInetLogRange IS NULL)
BEGIN
	SET @upperInetLogRange = @lowerInetLogRange + 25
END

SELECT TOP 1
	@MessageID = RTRIM(LTRIM(parameters))
FROM
	inetlog
WHERE
	LogTime >= @lowerDateTimeRange
	AND 
	LogTime <= @upperDateTimeRange
	AND 
	operation = 'DATA'
	--AND
	--servicestatus = 250
	AND
	InetLogID > @InetLogID

--PRINT '@MessageID:  ' + @MessageID
--PRINT '@lowerInetLogRange:  ' + CAST(@lowerInetLogRange AS varchar(20))
--PRINT '@upperInetLogRange:  ' + CAST(@upperInetLogRange AS varchar(20))

SELECT TOP 1
	@InetLogID AS InetLogID,
	parameters as MailTo, 
	LogTime AS [TimeStamp]
FROM
	inetlog
WHERE
	username = 'OutboundConnectionResponse' 
	AND 
	InetLogID BETWEEN @lowerInetLogRange AND @upperInetLogRange
ORDER BY
	InetLogID DESC
GO